﻿using Model;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Utility.Helper;

namespace BLL
{
    public class QX_PLANYEAR_DEPARTMENTBLL : BaseBLL
    {
        #region 实体操作

        public QX_PLANYEAR_DEPARTMENT Find(int id)
        {
            var sql = string.Format("SELECT * FROM QX_PLANYEAR_DEPARTMENT T1 WHERE T1.N_ID = {0}", id);
            var dt = Query(sql);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            var entity = new QX_PLANYEAR_DEPARTMENT();
            entity.N_ID = Convert.ToInt32(dt.Rows[0]["N_ID"]);
            entity.N_PROJECT_ID = Convert.ToInt32(dt.Rows[0]["N_PROJECT_ID"]);
            entity.C_PROJECT_NAME = dt.Rows[0]["C_PROJECT_NAME"].ToString();
            entity.N_PARENT_PROJECT_ID = Convert.ToInt32(dt.Rows[0]["N_PARENT_PROJECT_ID"]);
            entity.C_PARENT_PROJECT_NAME = dt.Rows[0]["C_PARENT_PROJECT_NAME"].ToString();
            entity.N_TYPE = Convert.ToInt32(dt.Rows[0]["N_TYPE"]);
            entity.N_MONEY = Convert.ToDecimal(dt.Rows[0]["N_MONEY"]);
            entity.N_APPLY_ID = Convert.ToInt32(dt.Rows[0]["N_APPLY_ID"]);
            entity.N_USER_ID = Convert.ToInt32(dt.Rows[0]["N_USER_ID"]);
            entity.C_REMARK = dt.Rows[0]["C_REMARK"].ToString();
            return entity;
        }

        public bool Insert(QX_PLANYEAR_DEPARTMENT entity)
        {
            entity.N_ID = (int)GetSequence("SEQ_YEARDEPARTMENT_ID");
            var sql = string.Format(@"INSERT INTO QX_PLANYEAR_DEPARTMENT(
	N_ID,
	N_PROJECT_ID,
	C_PROJECT_NAME,
	N_PARENT_PROJECT_ID,
	C_PARENT_PROJECT_NAME,
	N_TYPE,
	N_MONEY,
	N_APPLY_ID,
	N_USER_ID,
    C_REMARK,
    N_APPLY_TYPE)
VALUES(
	{0},
	{1},
	'{2}',
	{3},
	'{4}',
	{5},
	{6},
	{7},
	{8},
    '{9}',
    {10})",
    entity.N_ID,
    entity.N_PROJECT_ID,
    entity.C_PROJECT_NAME,
    entity.N_PARENT_PROJECT_ID == 0 ? "NULL" : entity.N_PARENT_PROJECT_ID.ToString(),
    entity.C_PARENT_PROJECT_NAME,
    entity.N_TYPE,
    entity.N_MONEY,
    entity.N_APPLY_ID,
    entity.N_USER_ID,
    entity.C_REMARK,
    entity.N_APPLY_TYPE);
            var count = NoQuery(sql);
            return count > 0 ? true : false;
        }

        public bool Update(QX_PLANYEAR_DEPARTMENT entity)
        {
            var sql = string.Format(@"UPDATE QX_PLANYEAR_DEPARTMENT
SET
	N_PROJECT_ID = {1},
	C_PROJECT_NAME = '{2}',
	N_PARENT_PROJECT_ID = {3},
	C_PARENT_PROJECT_NAME = '{4}',
	N_TYPE = {5},
	N_MONEY = {6},
	N_APPLY_ID = {7},
	N_USER_ID = {8},
    C_REMARK = '{9}',
    N_APPLY_TYPE = {10}
WHERE
	N_ID = {0}",
    entity.N_ID,
    entity.N_PROJECT_ID,
    entity.C_PROJECT_NAME,
    entity.N_PARENT_PROJECT_ID,
    entity.C_PARENT_PROJECT_NAME,
    entity.N_TYPE,
    entity.N_MONEY,
    entity.N_APPLY_ID,
    entity.N_USER_ID,
    entity.C_REMARK,
    entity.N_APPLY_TYPE);
            var count = NoQuery(sql);
            return count > 0 ? true : false;
        }

        public bool Delete(QX_PLANYEAR_DEPARTMENT entity)
        {
            var sql = string.Format("DELETE FROM QX_PLANYEAR_DEPARTMENT WHERE N_ID = {0}", entity.N_ID);
            return NoQuery(sql) > 0 ? true : false;
        }

        public List<QX_PLANYEAR_DEPARTMENT> ConvertToEntity(DataTable dt)
        {
            var entitys = new List<QX_PLANYEAR_DEPARTMENT>();
            foreach (var item in dt.AsEnumerable())
            {
                var entity = new QX_PLANYEAR_DEPARTMENT();
                entitys.Add(entity);
                entity.N_ID = Convert.ToInt32(item["N_ID"]);
                entity.N_PROJECT_ID = Convert.ToInt32(item["N_PROJECT_ID"]);
                entity.C_PROJECT_NAME = item["C_PROJECT_NAME"].ToString();

                if (item["N_PARENT_PROJECT_ID"].Equals(DBNull.Value))
                {
                    entity.N_PARENT_PROJECT_ID = null;
                }
                else
                {
                    entity.N_PARENT_PROJECT_ID = Convert.ToInt32(item["N_PARENT_PROJECT_ID"]);
                }

                entity.C_PARENT_PROJECT_NAME = item["C_PARENT_PROJECT_NAME"].ToString();
                entity.N_TYPE = Convert.ToInt32(item["N_TYPE"]);
                entity.N_MONEY = Convert.ToInt32(item["N_MONEY"]);
                entity.N_APPLY_ID = Convert.ToInt32(item["N_APPLY_ID"]);
                entity.N_USER_ID = Convert.ToInt32(item["N_USER_ID"]);
                entity.C_REMARK = item["C_REMARK"].ToString();
                entity.N_APPLY_TYPE = Convert.ToInt32(item["N_APPLY_TYPE"]);
            }
            return entitys;
        }

        #endregion 实体操作

        /// <summary>
        /// 获取用户的资金填写信息
        /// </summary>
        /// <param name="userId">用户Id</param>
        /// <param name="applyId">申请Id</param>
        /// <returns></returns>
        public List<QX_PLANYEAR_DEPARTMENT> Exites(int userId, int applyId)
        {
            var moduls = new List<QX_PLANYEAR_DEPARTMENT>();
            var sql = @"SELECT
                            T1.*
                        FROM QX_PLANYEAR_DEPARTMENT T1
                        WHERE
                            T1.N_APPLY_ID =:APPLY_ID AND
                            T1.N_USER_ID = :USER_ID
                        ORDER BY T1.N_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APPLY_ID", applyId, "int");
            parms.Add(":USER_ID", userId, "int");
            var dt = Query(sql, parms);
            if (dt.Rows.Count == 0)
            {
                //查询资金申请计划Project
                //查询Proejct信息表
                var projectBll = new QS_PROJECTBLL();

                sql = @"SELECT
                            T1.N_PROID AS PROJECT_ID,
                            T1.C_PRONAME AS PROJECT_NAME,
                            T1.N_TYPE AS TYPE,
                            T1.N_TYPE2 AS TYPE2,
                            nvl(T1.N_NUM1,0) AS ORDER_NUMBER1,
                            nvl(T1.N_NUM2,0) AS ORDER_NUMBER2,
                            T2.N_PROID AS PROJECT_PARENT_ID,
                            nvl(T2.C_PRONAME,'') AS PROJECT_PARENT_NAME
                        FROM QX_PLANYEAR_DETAIL T1
                        LEFT JOIN QX_PLANYEAR_DETAIL T2 ON T1.N_PARENTID = T2.N_PROID AND T1.N_YEARAPP_ID = T2.N_YEARAPP_ID
                        WHERE
                            T1.N_YEARAPP_ID =:APPLY_ID
                        ORDER BY T1.N_YDETAIL_ID";
                parms = new List<Tuple<string, string, string>>();
                parms.Add(":APPLY_ID", applyId, "int");
                foreach (var entity in Query(sql, parms).AsEnumerable())
                {
                    var item = new QX_PLANYEAR_DEPARTMENT();
                    moduls.Add(item);
                    item.N_PROJECT_ID = Convert.ToInt32(entity["PROJECT_ID"]);
                    item.C_PROJECT_NAME = entity["PROJECT_NAME"].ToString();

                    if (entity["PROJECT_PARENT_ID"].Equals(DBNull.Value) || Convert.ToInt32(entity["PROJECT_PARENT_ID"]) == 0)
                    {
                        item.N_PARENT_PROJECT_ID = 0;
                        item.C_PARENT_PROJECT_NAME = "";
                    }
                    else
                    {
                        item.N_PARENT_PROJECT_ID = Convert.ToInt32(entity["PROJECT_PARENT_ID"]);
                        item.C_PARENT_PROJECT_NAME = entity["PROJECT_PARENT_NAME"].ToString(); ;
                    }
                    item.N_TYPE = Convert.ToInt32(entity["TYPE"]);
                    //if (item.N_TYPE == 2)
                    //{
                    //    item.N_MONEY = null;
                    //}
                    //else
                    //{
                    //    item.N_MONEY = 0;
                    //}
                    item.N_MONEY = 0;
                    item.N_USER_ID = userId;
                    item.N_APPLY_ID = applyId;
                    item.N_APPLY_TYPE = Convert.ToInt32(entity["TYPE2"]);
                    Insert(item);
                }
            }
            else
            {
                moduls = ConvertToEntity(dt);
            }
            return moduls;
        }

        public void Go(int pid, JArray arry, List<QS_PROJECT> entitys)
        {
            var arry1 = entitys.Where(a => a.N_PARENTID == pid);

            //是否使用通用公司模板
            if (arry1.Where(a => a.CMY_ID != 0).Count() != 0)
            {
                arry1 = arry1.Where(a => a.CMY_ID != 0);
            }
            else
            {
                arry1 = arry1.Where(a => a.CMY_ID == 0);
            }

            foreach (var entity in arry1.OrderBy(a => a.N_NUM1).ThenBy(a => a.N_NUM2))
            {
                //var item = new QX_PLANYEAR_DEPARTMENT();
                var jObject = new JObject();
                arry.Add(jObject);
                jObject["N_PROJECT_ID"] = (int)entity.N_PROID;
                jObject["C_PROJECT_NAME"] = entity.C_PRONAME;
                if ((int)entity.N_PARENTID == 0)
                {
                    jObject["N_PARENT_PROJECT_ID"] = 0;
                    jObject["C_PARENT_PROJECT_NAME"] = "";
                }
                else
                {
                    jObject["N_PARENT_PROJECT_ID"] = (int)entity.N_PARENTID;
                    jObject["C_PARENT_PROJECT_NAME"] = entitys.Find(a => a.N_PROID == entity.N_PARENTID).C_PRONAME;
                }
                jObject["N_TYPE"] = (int)entity.N_TYPE;
                if (entity.N_TYPE == 2)
                {
                    jObject["N_MONEY"] = null;
                }
                else
                {
                    Go((int)entity.N_PROID, arry, entitys);
                    jObject["N_MONEY"] = 0;
                }
                //jObject.N_USER_ID = 0;
                //item.N_APPLY_ID = applyId;
            }
        }

        /// <summary>
        /// 通过资金申请Id获取所有汇合数据
        /// </summary>
        /// <param name="applyId"></param>
        /// <returns></returns>
        public JArray GetHz(int applyId)
        {
            var sql = @"SELECT
                            T1.N_PROID AS N_PROJECT_ID,
                            T1.N_PARENTID AS N_PARENT_PROJECT_ID,
                            0 AS N_MONEY,
                            '' AS C_REMARK
                        FROM QX_PLANYEAR_DETAIL T1
                        WHERE
                            T1.N_YEARAPP_ID = :APPLY_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APPLY_ID", applyId, "int");
            var arry = JArray.FromObject(Query(sql, parms));

            //查询所有部门人员填写的申请信息。
            var data = GetDataAll(applyId);

            //查询所有用户信息
            var useBll = new Sys_UserBLL();
            //进行分组添加数据
            foreach (var item in data.AsEnumerable().GroupBy(a => a.N_PROJECT_ID))
            {
                var jObject = arry.FirstOrDefault(a => a["N_PROJECT_ID"].ToObject<int>() == item.Key);
                jObject["N_MONEY"] = item.Where(a => a.N_MONEY != null).ToList().Sum(a => a.N_MONEY);
                //抓取所有备注
                var str = "";
                foreach (var item1 in item)
                {
                    if (item1.C_REMARK == null || item1.C_REMARK.Trim() == "")
                    {
                        continue;
                    }
                    if (str != "" && item1.C_REMARK.Substring(item1.C_REMARK.Length - 1, 1) != ",")
                    {
                        str += ",";
                    }
                    var depName = new Sys_DepartmentBLL().GetInfoById(new Sys_UserBLL().Find(item1.N_USER_ID).USER_DEPARTMENT.ToString()).DEPTNAME;
                    str += depName + "," + item1.C_REMARK;
                }
                jObject["C_REMARK"] = str;
            }

            return arry;
        }

        public void Update(int userId, int applyId, JArray arry)
        {
            try
            {
                var sqlList = new List<Tuple<string, List<Tuple<string, string, string>>>>();
                foreach (var item in arry)
                {
                    var sql = @"UPDATE QX_PLANYEAR_DEPARTMENT
                                SET
                                    N_MONEY = :N_MONEY,
                                    C_REMARK = :C_REMARK
                                WHERE
                                    N_PROJECT_ID =:PROJECT_ID AND
                                    N_APPLY_ID = :APPLY_ID AND
                                    N_USER_ID = :USER_ID";
                    var parms = new List<Tuple<string, string, string>>();
                    parms.Add(":N_MONEY", item["N_MONEY"].ToObject<decimal?>(), "decimal");
                    parms.Add(":C_REMARK", item["C_REMARK"].ToObject<string>(), "string");
                    parms.Add(":PROJECT_ID", item["N_PROJECT_ID"].ToObject<int>(), "int");
                    parms.Add(":APPLY_ID", applyId, "int");
                    parms.Add(":USER_ID", userId, "int");
                    sqlList.Add(new Tuple<string, List<Tuple<string, string, string>>>(sql, parms));
                }
                var count = SQLSExcute(sqlList);
            }
            catch (Exception ex)
            {
                throw new Exception("更新部门资金计划编制信息异常");
            }
        }

        /// <summary>
        /// 通过申请Id查询所有用户填写的资金申请计划
        /// </summary>
        /// <param name="applyId"></param>
        /// <returns></returns>
        public List<QX_PLANYEAR_DEPARTMENT> GetDataAll(int applyId)
        {
            //var sql = "SELECT * FROM QX_PLANYEAR_DEPARTMENT T1 WHERE T1.N_TYPE = 2 AND T1.N_APPLY_ID = " + applyId;
            var sql = "SELECT * FROM QX_PLANYEAR_DEPARTMENT T1 WHERE T1.N_APPLY_ID = " + applyId;
            var dt = Query(sql);
            return ConvertToEntity(dt);
        }

        /// <summary>
        /// 通过申请Id 查询 申请人员填写的内容
        /// </summary>
        /// <param name="applyId"></param>
        /// <returns>JArry
        ///  Int UserId 用户Id
        ///  String UserName 用户名
        ///  Int DepartmentId 部门Id
        ///  String DepartmentName 部门名
        ///  Decimal InMoney 总金额（流入）
        ///  Decimal OutMoney 总金额（流出）
        /// </returns>
        public JArray GetDataByApplyId(int applyId)
        {
            var arry = new JArray();
            var data = GetDataAll(applyId);
            var userBll = new Sys_UserBLL();
            var departmentBll = new Sys_DepartmentBLL();
            foreach (var item in data.GroupBy(a => a.N_USER_ID))
            {
                var obj = new JObject();
                arry.Add(obj);

                obj["UserId"] = item.Key;
                var user = userBll.Find(item.Key);
                obj["UserName"] = user == null ? "" : user.USER_NAME;
                Sys_Department department = null;
                if (user != null)
                {
                    department = departmentBll.GetInfoById(user.USER_DEPARTMENT.ToString());
                }
                obj["DepartmentId"] = null;
                if (department != null)
                {
                    obj["DepartmentId"] = department.DEPTID;
                }
                obj["DepartmentName"] = department == null ? "" : department.DEPTNAME;
                obj["InMoney"] = item.Where(a => a.N_APPLY_TYPE == 1 && a.N_TYPE == 2 && a.N_MONEY != null).Sum(a => a.N_MONEY);
                obj["OutMoney"] = item.Where(a => a.N_APPLY_TYPE == 2 && a.N_TYPE == 2 && a.N_MONEY != null).Sum(a => a.N_MONEY);
            }
            return arry;
        }
    }
}